﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="MFGetDirectoryList_FillRow", IsDeterministic:=False, IsPrecise:=False, TableDefinition:="FilePath nvarchar(128), FileName nvarchar(128), FileNameWithPath nvarchar(255)")> _
    Public Shared Function MFGetDirectoryList(ByVal Directory As String) As IEnumerable
        Try
            Dim _oDir As New System.IO.DirectoryInfo(Directory)

            Return _oDir.GetFiles("*.*")
        Catch sqlex As SqlException
            Throw New Exception("", sqlex)
        Catch ex As Exception
            Throw New Exception("", ex)
        End Try
    End Function

    Shared Sub MFGetDirectoryList_FillRow(ByVal obj As Object, <Out()> ByRef FilePath As SqlString, <Out()> ByRef FileName As SqlString, <Out()> ByRef FileNameWithPath As SqlString)
        Try
            Dim _oFileInfo As System.IO.FileInfo = CType(obj, System.IO.FileInfo)

            If Not _oFileInfo.DirectoryName.EndsWith("\") Then
                FilePath = _oFileInfo.DirectoryName.Trim & "\"
            Else
                FilePath = _oFileInfo.DirectoryName
            End If

            FileName = _oFileInfo.Name
            FileNameWithPath = _oFileInfo.FullName
        Catch sqlex As SqlException
            Throw New Exception("", sqlex)
        Catch ex As Exception
            Throw New Exception("", ex)
        End Try
    End Sub

End Class
